#!/bin/bash

APP=gmall

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else
    do_date=`date -d "-1 day" +%F`
fi

common_sql="use ${APP};"

ads_traffic_stats_by_channel_sql="
    with total_data as (
    select
        channel,

        -- 最近一天的数据
        count(distinct if (
            dt >= '${do_date}', mid_id, null)
        ) uv_count_1d,
        avg(
            if (dt >= '${do_date}', during_time_1d, 0)
        ) / 1000 avg_duration_sec_1d,
        avg(
            if (dt >= '${do_date}', page_count_1d, 0)
        ) avg_page_count_1d,
        sum(
            if (dt >= '${do_date}', 1, 0)
        ) sv_count_1d,
        sum(
            if (dt >= '${do_date}' and page_count_1d = 1, 1, 0)
        ) / sum(
            if (dt >= '${do_date}', 1, 0)
        ) bounce_rate_1d,

        -- 最近七天的数据
        count(distinct if (
            dt >= date_sub('${do_date}', 6), mid_id, null)
        ) uv_count_7d,
        avg(
            if (dt >= date_sub('${do_date}', 6), during_time_1d, 0)
        ) / 1000 avg_duration_sec_7d,
        avg(
            if (dt >= date_sub('${do_date}', 6), page_count_1d, 0)
        ) avg_page_count_7d,
        sum(
            if (dt >= date_sub('${do_date}', 6), 1, 0)
        ) sv_count_7d,
        sum(
            if (dt >= date_sub('${do_date}', 6) and page_count_1d = 1, 1, 0)
        ) / sum(
            if (dt >= date_sub('${do_date}', 6), 1, 0)
        ) bounce_rate_7d,

        -- 最近三十天的数据
        count(distinct if (
            dt >= date_sub('${do_date}', 29), mid_id, null)
        ) uv_count_30d,
        avg(
            if (dt >= date_sub('${do_date}', 29), during_time_1d, 0)
        ) / 1000 avg_duration_sec_30d,
        avg(
            if (dt >= date_sub('${do_date}', 29), page_count_1d, 0)
        ) avg_page_count_30d,
        sum(
            if (dt >= date_sub('${do_date}', 29), 1, 0)
        ) sv_count_30d,
        sum(
            if (dt >= date_sub('${do_date}', 29) and page_count_1d = 1, 1, 0)
        ) / sum(
            if (dt >= date_sub('${do_date}', 29), 1, 0)
        ) bounce_rate_30d
        from dws_traffic_session_page_view_1d
        where dt <= '${do_date}' and dt >= date_sub(dt, 29)
        group by channel
    )
    insert overwrite table ads_traffic_stats_by_channel
    select * from ads_traffic_stats_by_channel where dt != '${do_date}'
    union
    select
        '${do_date}',
        recent_days,
        channel,
        case recent_days
            when 1 then uv_count_1d
            when 7 then uv_count_7d
            else uv_count_30d
        end as uv_count,
        case recent_days
            when 1 then avg_duration_sec_1d
            when 7 then avg_duration_sec_7d
            else avg_duration_sec_30d
        end as avg_duration_sec,
        case recent_days
            when 1 then avg_page_count_1d
            when 7 then avg_page_count_7d
            else avg_page_count_30d
        end as avg_page_count,
        case recent_days
            when 1 then sv_count_1d
            when 7 then sv_count_7d
            else sv_count_30d
        end as sv_count,
        case recent_days
            when 1 then bounce_rate_1d
            when 7 then bounce_rate_7d
            else bounce_rate_30d
        end as bounce_rate
    from total_data lateral view explode(array(1, 7, 30)) tmp as recent_days;
"

ads_page_path_sql="
    with path_info as (
        select
            page_id source,
            lead(page_id, 1) over(partition by session_id order by view_time) next_page_id,
            row_number() over (partition by session_id order by view_time) rk
        from dwd_traffic_page_view_inc
    ), full_name_path_info as (
        select
            concat('step-', rk, ':', source) as source,
            concat('step-', rk + 1, ':', next_page_id) as target
        from path_info
    )
    insert overwrite table ads_page_path
    select * from ads_page_path
    union
    select
        '${do_date}' dt,
        source,
        nvl(target, 'null') as target,
        count(1) path_count
    from full_name_path_info
    group by source, target;
"

ads_user_change_sql="
    with today_churn_info as (
        select count(*) as user_churn_count
        from dws_user_user_login_td
        where dt = '${do_date}' and login_last_date = date_sub('${do_date}', 7)
    ), today_login as (
        select
            user_id,
            login_last_date
        from dws_user_user_login_td
        where dt = '${do_date}' and login_last_date = '${do_date}'
    ), last_login as (
        select
            user_id,
            login_last_date
        from dws_user_user_login_td
        where dt = date_sub('${do_date}', 1) and login_last_date <= date_sub('${do_date}', 8)
    ), today_back_info as (
        select count(*) user_back_count
        from today_login
        join last_login on today_login.user_id = last_login.user_id
    )
    insert overwrite table ads_user_change
    select * from ads_user_change
    union
    select
        '${do_date}' as dt,
        user_churn_count,
        user_back_count
    from today_churn_info
    join today_back_info;
"

ads_user_retention_sql="
    with create_info as (
        select
            user_id,
            date_id create_date
        from dwd_user_register_inc
        where dt >= date_sub('${do_date}', 7) and dt < '${do_date}'
    ), login_info as (
        select
            user_id,
            login_last_date
        from dws_user_user_login_td
        where dt = '${do_date}'
    ), date_info as (
        select
            create_info.user_id,
            create_date,
            login_last_date
        from create_info
        join login_info on create_info.user_id = login_info.user_id
    )
    insert overwrite table ads_user_retention
    select * from ads_user_retention
    union
    select
        '${do_date}' dt,
        create_date,
        datediff('${do_date}', create_date) as retention_day,
        count(*) new_user_count,
        sum(if (login_last_date = '${do_date}', 1, 0)) retention_count,
        cast(sum(if (login_last_date = '${do_date}', 1, 0)) / count(*) * 100 as decimal(16, 2)) as retention_rate
    from date_info
    group by create_date;
"

ads_user_stats_sql="
    with new_user_info as (
        select
            recent_days,
            count(*) new_user_count
        from dwd_user_register_inc lateral view explode(array(1, 7, 30)) tmp as recent_days
        where dt >= date_sub('${do_date}', 30)
            and dt >= date_sub('${do_date}', recent_days - 1)
        group by recent_days
    ), active_user_info as (
        select
            recent_days,
            count(if (login_last_date >= date_sub('${do_date}', recent_days - 1), 1, null)) active_user_count
        from dws_user_user_login_td lateral view explode(array(1, 7, 30)) tmp as recent_days
        where dt = '${do_date}'
        group by recent_days
    )
    insert overwrite table ads_user_stats
    select * from ads_user_stats
    union
    select
        '${do_date}' dt,
        nvl(new_user_info.recent_days, active_user_info.recent_days) as recent_days,
        nvl(new_user_info.new_user_count, 0) as new_user_count,
        nvl(active_user_info.active_user_count, 0) as active_user_count
    from new_user_info
    full join active_user_info on new_user_info.recent_days = active_user_info.recent_days;
"

ads_user_action_sql="
    with page_info as (
        select
            sum(if (page_id = 'home', 1, 0)) as home_count,
            sum(if (page_id = 'good_detail', 1, 0)) as good_detail_count
        from dws_traffic_page_visitor_page_view_1d
        where dt = '${do_date}'
            and (page_id = 'home' or page_id = 'good_detail')
    ), cart_info as (
        select
            count(cart_add_count_1d) cart_count
        from dws_trade_user_cart_add_1d
        where dt = '${do_date}'
    ), order_info as (
        select
            count(order_count_1d) order_count
        from dws_trade_user_order_1d
        where dt = '${do_date}'
    ), payment_info as (
        select
            count(payment_count_1d) payment_count
        from dws_trade_user_payment_1d
        where dt = '${do_date}'
    )
    insert overwrite table ads_user_action
    select * from ads_user_action
    union
    select
        '${do_date}' dt,
        home_count,
        good_detail_count,
        cart_count,
        order_count,
        payment_count
    from page_info
    join cart_info
    join order_info
    join payment_info;
"

ads_new_order_user_stats_sql="
    insert overwrite table ads_new_order_user_stats
    select * from ads_new_order_user_stats
    union
    select
        '${do_date}' as dt,
        recent_days,
        count(*) new_order_user_count
    from dws_trade_user_order_td lateral view explode(array(1, 7, 30)) tmp as recent_days
    where dt = '${do_date}'
      and order_date_first >= date_sub('${do_date}', recent_days - 1)
    group by recent_days;
"

ads_order_continuously_user_count_sql="
    insert overwrite table ads_order_continuously_user_count
    select * from ads_order_continuously_user_count
    union
    select
        '${do_date}' as dt,
        7,
        count(distinct user_id) as order_continuously_user_count
    from (
        select
            user_id,
            datediff(lead(dt, 2, '9999-12-31') over(partition by user_id order by dt), dt) diff
        from dws_trade_user_order_1d
        where dt >= date_sub('${do_date}', 6) and dt <= '${do_date}'
    ) t1
    where diff = 2;
"

ads_repeat_purchase_by_tm_sql="
    with order_info as (
        select
            user_id, tm_id, tm_name, sum(order_count_30d) order_count
        from dws_trade_user_sku_order_nd
        where dt = '${do_date}'
        group by user_id, tm_id, tm_name
    )
    insert overwrite table ads_repeat_purchase_by_tm
    select * from ads_repeat_purchase_by_tm
    union
    select
        '${do_date}' as dt,
        30,
        tm_id,
        tm_name,
        cast(sum(if (order_count >= 2, 1, 0)) / sum(if (order_count >= 1, 1, 0)) as decimal(16, 2)) * 100 as order_repeat_rate
    from order_info
    group by tm_id, tm_name;
"

ads_order_stats_by_tm_sql="
    with order_1d as (
        select
            tm_id,
            tm_name,
            sum(order_count_1d) as order_count_1d,
            count(distinct user_id) as order_user_count_1d
        from dws_trade_user_sku_order_1d
        where dt = '${do_date}'
        group by tm_id, tm_name
    ), order_nd as (
        select
            tm_id,
            tm_name,
            sum(order_count_7d) as order_count_7d,
            count(distinct if(order_count_7d > 0, user_id, null)) as order_user_count_7d,
            sum(order_count_30d) as order_count_30d,
            count(distinct user_id) as order_user_count_30d
        from dws_trade_user_sku_order_nd
        where dt = '${do_date}'
        group by tm_id, tm_name
    ), join_result as (
        select
            order_1d.tm_id,
            order_1d.tm_name,
            order_count_1d,
            order_user_count_1d,
            nvl(order_count_7d, 0) as order_count_7d,
            nvl(order_user_count_7d, 0) as order_user_count_7d,
            nvl(order_count_30d, 0) as order_count_30d,
            nvl(order_user_count_30d, 0) as order_user_count_30d
        from order_1d
        left join order_nd on order_1d.tm_id = order_nd.tm_id
    )
    insert overwrite table ads_order_stats_by_tm
    select * from ads_order_stats_by_tm
    union
    select
        '${do_date}' as dt,
        recent_days,
        tm_id,
        tm_name,
        case recent_days
            when 1 then order_count_1d
            when 7 then order_count_7d
            else order_count_30d
        end as order_count,
        case recent_days
            when 1 then order_user_count_1d
            when 7 then order_user_count_7d
            else order_user_count_30d
            end as order_user_count
    from join_result lateral view explode(array(1, 7, 30)) tmp as recent_days;
"

ads_order_stats_by_cate_sql="
    with order_1d as (
        select
            category1_id,
            category1_name,
            category2_id,
            category2_name,
            category3_id,
            category3_name,
            sum(order_count_1d) as order_count_1d,
            count(distinct user_id) as order_user_count_1d
        from dws_trade_user_sku_order_1d
        where dt = '${do_date}'
        group by category1_id, category1_name, category2_id, category2_name, category3_id, category3_name
    ), order_nd as (
        select
            category1_id,
            category1_name,
            category2_id,
            category2_name,
            category3_id,
            category3_name,
            sum(order_count_7d) as order_count_7d,
            count(distinct if(order_count_7d > 0, user_id, null)) as order_user_count_7d,
            sum(order_count_30d) as order_count_30d,
            count(distinct user_id) as order_user_count_30d
        from dws_trade_user_sku_order_nd
        where dt = '${do_date}'
        group by category1_id, category1_name, category2_id, category2_name, category3_id, category3_name
    ), join_result as (
        select
            order_1d.category1_id,
            order_1d.category1_name,
            order_1d.category2_id,
            order_1d.category2_name,
            order_1d.category3_id,
            order_1d.category3_name,
            order_count_1d,
            order_user_count_1d,
            nvl(order_count_7d, 0) as order_count_7d,
            nvl(order_user_count_7d, 0) as order_user_count_7d,
            nvl(order_count_30d, 0) as order_count_30d,
            nvl(order_user_count_30d, 0) as order_user_count_30d
        from order_1d
         left join order_nd on order_1d.category3_id = order_nd.category3_id
    )
    insert overwrite table ads_order_stats_by_cate
    select * from ads_order_stats_by_cate
    union
    select
        '${do_date}' as dt,
        recent_days,
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id,
        category3_name,
        case recent_days
            when 1 then order_count_1d
            when 7 then order_count_7d
            else order_count_30d
            end as order_count,
        case recent_days
            when 1 then order_user_count_1d
            when 7 then order_user_count_7d
            else order_user_count_30d
            end as order_user_count
    from join_result lateral view explode(array(1, 7, 30)) tmp as recent_days;
"

ads_sku_cart_num_top3_by_cate_sql="
    with cart_info as (
        select
            sku_id,
            sku_name,
            sum(sku_num) cart_num
        from dwd_trade_cart_full
        where dt = '${do_date}'
        group by sku_id, sku_name
    ), sku_info as (
        select
            id,
            category1_id,
            category1_name,
            category2_id,
            category2_name,
            category3_id,
            category3_name
        from dim_sku_full
        where dt = '${do_date}'
    ), join_result as (
        select
            category1_id,
            category1_name,
            category2_id,
            category2_name,
            category3_id,
            category3_name,
            sku_id,
            sku_name,
            cart_num,
            row_number() over (partition by category3_id order by cart_num desc) rk
        from cart_info
        left join sku_info on cart_info.sku_id = sku_info.id
    )
    insert overwrite table ads_sku_cart_num_top3_by_cate
    select * from ads_sku_cart_num_top3_by_cate
    union
    select
        '${do_date}' as dt,
        category1_id,
        category1_name,
        category2_id,
        category2_name,
        category3_id,
        category3_name,
        sku_id,
        sku_name,
        cart_num,
        rk
    from join_result where rk <= 3;
"

ads_sku_favor_count_top3_by_tm_sql="
    with favor_info as (
        select
            tm_id, tm_name, sku_id, sku_name, favor_add_count_1d,
            row_number() over (partition by tm_id order by favor_add_count_1d desc) rk
        from dws_interaction_sku_favor_add_1d
        where dt = '${do_date}'
    )
    insert overwrite table ads_sku_favor_count_top3_by_tm
    select * from ads_sku_favor_count_top3_by_tm
    union
    select
        '${do_date}' as dt, tm_id, tm_name, sku_id, sku_name, favor_add_count_1d, rk
    from favor_info where rk <= 3;
"

ads_order_to_pay_interval_avg_sql="
    insert overwrite table ads_order_to_pay_interval_avg
    select * from ads_order_to_pay_interval_avg
    union
    select
        '${do_date}' as dt,
        avg(to_unix_timestamp(payment_time) - to_unix_timestamp(order_time)) order_to_pay_interval_avg
    from dwd_trade_trade_flow_acc
    where (dt = '9999-12-31' or dt = '${do_date}') and order_date_id <= '${do_date}' and payment_time is not null;
"

ads_order_by_province_sql="
    with order_1d as (
        select
            province_id,
            province_name,
            area_code,
            iso_code,
            iso_3166_2,
            sum(order_count_1d) as order_count_1d,
            sum(order_total_amount_1d) as order_total_amount_1d
        from dws_trade_province_order_1d
        where dt = '${do_date}'
        group by province_id, province_name, area_code, iso_code, iso_3166_2
    ), order_nd as (
        select
            province_id,
            province_name,
            area_code,
            iso_code,
            iso_3166_2,
            sum(order_count_7d) as order_count_7d,
            sum(order_total_amount_7d) as order_total_amount_7d,
            sum(order_count_30d) as order_count_30d,
            sum(order_total_amount_30d) as order_total_amount_30d
        from dws_trade_province_order_nd
        where dt = '${do_date}'
        group by province_id, province_name, area_code, iso_code, iso_3166_2
    ), join_result as (
        select
            order_1d.province_id,
            order_1d.province_name,
            order_1d.area_code,
            order_1d.iso_code,
            order_1d.iso_3166_2,
            order_count_1d,
            order_total_amount_1d,
            nvl(order_count_7d, 0) order_count_7d,
            nvl(order_total_amount_7d, 0) order_total_amount_7d,
            nvl(order_count_30d, 0) order_count_30d,
            nvl(order_total_amount_30d, 0) order_total_amount_30d
        from order_1d
        left join order_nd on order_1d.province_id = order_nd.province_id
    )
    insert overwrite table ads_order_by_province
    select * from ads_order_by_province
    union
    select
        '${do_date}' dt,
        recent_days,
        province_id,
        province_name,
        area_code,
        iso_code,
        iso_3166_2,
        case recent_days
            when 1 then order_count_1d
            when 7 then order_count_7d
            else order_count_30d
        end as order_count,
        case recent_days
            when 1 then order_total_amount_1d
            when 7 then order_total_amount_7d
            else order_total_amount_30d
        end as order_total_amount
    from join_result lateral view explode(array(1, 7, 30)) tmp as recent_days;
"

ads_coupon_stats_sql="
    insert overwrite table ads_coupon_stats
    select * from ads_coupon_stats
    union
    select
        '${do_date}' dt,
        coupon_id,
        coupon_name,
        sum(used_count_1d),
        count(distinct user_id)
    from dws_tool_user_coupon_coupon_used_1d
    where dt = '${do_date}'
    group by coupon_id, coupon_name;
"

# 判断表名参数，表名参数是第一个参数
case $1 in
"ads_traffic_stats_by_channel")
    hive -e "${common_sql}${ads_traffic_stats_by_channel_sql}"
;;
"ads_page_path")
    hive -e "${common_sql}${ads_page_path_sql}"
;;
"ads_user_change")
    hive -e "${common_sql}${ads_user_change_sql}"
;;
"ads_user_retention")
    hive -e "${common_sql}${ads_user_retention_sql}"
;;
"ads_user_stats")
    hive -e "${common_sql}${ads_user_stats_sql}"
;;
"ads_user_action")
    hive -e "${common_sql}${ads_user_action_sql}"
;;
"ads_new_order_user_stats")
    hive -e "${common_sql}${ads_new_order_user_stats_sql}"
;;
"ads_order_continuously_user_count")
    hive -e "${common_sql}${ads_order_continuously_user_count_sql}"
;;
"ads_repeat_purchase_by_tm")
    hive -e "${common_sql}${ads_repeat_purchase_by_tm_sql}"
;;
"ads_order_stats_by_tm")
    hive -e "${common_sql}${ads_order_stats_by_tm_sql}"
;;
"ads_order_stats_by_cate")
    hive -e "${common_sql}${ads_order_stats_by_cate_sql}"
;;
"ads_sku_cart_num_top3_by_cate")
    hive -e "${common_sql}${ads_sku_cart_num_top3_by_cate_sql}"
;;
"ads_sku_favor_count_top3_by_tm")
    hive -e "${common_sql}${ads_sku_favor_count_top3_by_tm_sql}"
;;
"ads_order_to_pay_interval_avg")
    hive -e "${common_sql}${ads_order_to_pay_interval_avg_sql}"
;;
"ads_order_by_province")
    hive -e "${common_sql}${ads_order_by_province_sql}"
;;
"ads_coupon_stats")
    hive -e "${common_sql}${ads_coupon_stats_sql}"
;;
"all")
    hive -e "${common_sql}${ads_traffic_stats_by_channel_sql}${ads_page_path_sql}${ads_user_change_sql}${ads_user_retention_sql}${ads_user_stats_sql}${ads_user_action_sql}${ads_new_order_user_stats_sql}${ads_order_continuously_user_count_sql}${ads_repeat_purchase_by_tm_sql}${ads_order_stats_by_tm_sql}${ads_order_stats_by_cate_sql}${ads_sku_cart_num_top3_by_cate_sql}${ads_sku_favor_count_top3_by_tm_sql}${ads_order_to_pay_interval_avg_sql}${ads_order_by_province_sql}${ads_coupon_stats_sql}"
;;
esac